#!/usr/bin/env bash

base_path=$(dirname $0)
dt15=`date -d "15 days ago "  "+%Y-%m-%d"`
#echo ${base_path}

mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
select
       t1.day,t1.station_id,t1.product_code,
       oilMoney,
       oilLitre,case when ordinary_discount is null then 0 else ordinary_discount end ordinary_discount
from (
        select substr(X.transaction_date,1,10) day,X.station_id,X.product_code product_code,
        sum(X.item_quantity) oilLitre,sum(X.item_value) oilMoney
       from
       (select Z.transaction_date,Y.station_id,Y.transaction_id,trim(Y.product_code) product_code,
       Y.item_quantity,Y.item_value,Z.sale_total
       from transactions as Z
       join transaction_items_oil as Y
       on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
       where substr(Z.transaction_date,1,10)>='$dt15'
       )AS X
       group by substr(X.transaction_date,1,10),X.station_id,X.product_code
)  t1
left join
       (
       select day,station_id,trim(product_code) product_code,sum(ordinary_discount) ordinary_discount from
          (
          select substr(A.transaction_date,1,10) day,A.station_id,'非油' product_code,B.item_value ordinary_discount
          from
          transactions as A
          JOIN transaction_items_mop AS B
          join transaction_items_notoil as Y
          on A.transaction_id = B.transaction_id and A.station_id = B.station_id and B.item_description='优惠券'
          and A.transaction_id=Y.transaction_id and A.station_id=Y.station_id
          where substr(A.transaction_date,1,10)>='$dt15'
          union all
          select substr(A.transaction_date,1,10) day,A.station_id,Y.product_code,B.item_value  ordinary_discount
          from
          transactions   as A
          JOIN transaction_items_mop AS B
          join transaction_items_oil as Y
          on A.transaction_id = B.transaction_id and A.station_id = B.station_id and B.item_description='优惠券'
          and A.transaction_id=Y.transaction_id and A.station_id=Y.station_id
          where substr(A.transaction_date,1,10)>='$dt15'
          ) tmp group by day,station_id,trim(product_code)
       ) t2
on t1.day=t2.day and t1.station_id=t2.station_id and t1.product_code=t2.product_code;
EOF
